//
//  ZLSQLiteManager.swift
//  数据库
//
//  Created by zhanglongtao on 17/1/17.
//  Copyright © 2017年 hanju001. All rights reserved.
//

import Foundation
import FMDB

//最大的数据库缓存时间 s为单位
private let maxDBCacheTime: TimeInterval = -5 * 24 * 60 * 60
/**
 1.数据库本质是保存在沙河中的一个文件，首先创建爱你并且打开数据库
 2.创建数据表
 3.增删该查
 
 提示：数据库开发，程序代码几乎都是一致的，区别在sql
 测试sql的正确性
 */
class ZLSQLiteManager {
    
    //单例 全局数据库工具访问点
    static let shared = ZLSQLiteManager()
    
    let queue: FMDatabaseQueue
    //构造函数
    private init() {
        
        //数据库的全路径
        let dbName = "status.db"
        var path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
        path = (path as NSString).appendingPathComponent(dbName)
        
        //创建数据库队列 同时创建或者打开数据库
        queue = FMDatabaseQueue(path: path)
        
        //打开数据库
        createTable()
        
        //注册通知
        //模仿SDWebImage
        NotificationCenter.default.addObserver(self, selector: #selector(clearDBCache), name: NSNotification.Name.UIApplicationDidEnterBackground, object: nil)
    }
    @objc private func clearDBCache() {
        
       
        let dateString = Date.zl_dateString(delta: maxDBCacheTime)
        
        let sql = "DELETE * FROM T_Status WHERE createTime < '?';"
        
        queue.inDatabase { (db) in
            
            if db?.executeUpdate(sql, withArgumentsIn: [dateString]) == true{
             
                print("删除了\(db?.changes())条数据")
            }
            
        }
        
    }
    deinit {
        
        NotificationCenter.default.removeObserver(self)
    }
}
//微博数据操作
extension ZLSQLiteManager {
 
    //从数据库中加载微博数据
    func loadStatus(userId: String, since_id: Int64,max_id: Int64) -> [[String: AnyObject]] {
        
        var sql = "SELECT statusId, userId, status FROM T_Status \n"
        
        sql += "WHERE userId = \(userId) \n"
        if since_id > 0 {
            sql += "AND statusId > \(since_id) \n"
        }else if max_id > 0 {
            sql += "AND statusId < \(max_id) \n"
        }
        
        sql += "ORDER BY statusId DESC LIMIT 20;"
        
        let array = execuRecordSet(sql: sql)
        
        //遍历数组反序列化
        var result = [[String: AnyObject]]()
        for dict in array{
            
            guard let jsonData = dict["status"] as? Data, let json = try? JSONSerialization.jsonObject(with: jsonData, options: []) as? [String: AnyObject] else{
                
                continue
            }
            
            //追加到数组
            result.append(json ?? [:])
        
        }
        
        print(sql)
        return result
    }
    
    //新增微博数据，微博数据在刷新的时候，可能会出现重叠
    func updateStatus(userId: String, array: [[String: AnyObject]]) {
        
        /**
         status 是完整微博字典的json 二进制数据
         */
        let sql = "INSERT OR REPLACE INTO T_Status (statusId, userId, status) VALUES (?, ?, ?);"
        
        queue.inTransaction { (db, rollback) in
            
            //遍历数组 逐插入微博数据
            for dict in array {
                
                //将字典序列化成二进制数据
                guard let statusId = dict["idstr"] as? String, let jsonData = try? JSONSerialization.data(withJSONObject: dict, options: []) else{
                    
                    continue
                }
                
                //执行
                if db?.executeUpdate(sql, withArgumentsIn: [statusId, userId, jsonData]) == false{
                    
                    //需要回滚
                    rollback?.pointee = true
                    break
                }
                
               
            }
        }
        
    }
}
private extension ZLSQLiteManager {
    
    //执行一个sql返回一个字典的数组
    func execuRecordSet(sql: String) -> [[String: AnyObject]] {
        
        var result = [[String: AnyObject]]()
        
        //执行sql
        queue.inDatabase { (db) in
            
            guard let rs = db?.executeQuery(sql, withArgumentsIn: []) else{
                
                return
            }
            while rs.next() {
                
                //列数
                let colCount = rs.columnCount()
                
                for col in 0..<colCount{
                    
                    guard let name = rs.columnName(for: col), let  value = rs.object(forColumnIndex: col) else{
                        
                        continue
                    }
                    
                    result.append([name: value as AnyObject])
                    
                }
                
            }
        }
        
        return result
    }
    
    //创建数据库表
    func createTable() {
        
        guard let path = Bundle.main.path(forResource: "status.sql", ofType: nil), let sql = try? String(contentsOfFile: path) else{
            
            return
        }
       
        //执行
        queue.inDatabase { (db) in
            
            if db?.executeStatements(sql) == true {
                
                print("创建表成功")
            }else{
                print("创建表失败")
            }
        }
        
    }
    
}
